package com.class3.team.fossilProject.Dao;

import com.class3.team.fossilProject.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


//////////这个地方需要再改一次访问JDBC////43行！康姐记得改密码
@Repository
public class BookDaoImp implements BookDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Book>  findByTime(double start_year, double end_year) {
        System.out.println("接受到数据 " + start_year + " " + end_year);
        String sql = "select * from fossil.book where (start_year < " + end_year + " && start_year > " + end_year +
        ") || (start_year > " + start_year + " && end_year < " + start_year +
        ") || (end_year > " + start_year + " && start_year < " + end_year +")";
        RowMapper<Book> rowMapper=new BeanPropertyRowMapper<Book>(Book.class);
        List<Book> res= jdbcTemplate.query(sql, rowMapper);
        
        for(int i = 0; i < res.size(); i++) {
        	System.out.println(res.get(i));
        }
        
        System.out.println("大小！ " + res.size());
//        for(int i = 0; i < res.size(); i++){
//            System.out.println("123456");
//            System.out.println(res.get(i).toString());
//        }
        return res;
    }

    @Override
    public List<Book> searchForHeatmap() {
        List<Book> res = new ArrayList<>();
        System.out.println("开始搜索热力图");
        String sql = "SELECT modern_longitude, modern_latitude, COUNT(*) as count FROM book group by modern_latitude, modern_longitude; ";
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/fossil?serverTimezone=GMT%2B8", "root", "123456");
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Book book = new Book();
                System.out.println(resultSet.getString("modern_latitude"));
                book.setModernLatitude(resultSet.getString("modern_latitude"));
                book.setModernLongitude(resultSet.getString("modern_longitude"));
                book.setCount(Integer.parseInt(resultSet.getString("count")));
                //System.out.println(resultSet.getString("count"));
                res.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
}

